import pymysql
from collections import Counter
import os
import base64
from collections import defaultdict
import concurrent.futures


# 获取环境变量的db_password
# 获得数据库中的数据
def database2data():
    connection = pymysql.connect(
        host="118.89.122.209",
        port=3306,
        user='select_user',
        password='1234',
        db='poav_db',
    )
    try:
        with connection.cursor() as cursor:
            sql = """
            SELECT user2doc.document,DATE_FORMAT(user2doc.date, '%Y-%m-%d') AS date_str,doc2topics.topic2,doc2topics.key_words2,doc2topics.topic1,doc2topics.key_words1
            FROM user2doc
            INNER JOIN doc2topics
            ON doc2topics.docID = user2doc.documentID
            WHERE doc2topics.topic2 != -1
            """
            cursor.execute(sql)
            result = cursor.fetchall()
    finally:
        connection.close()
    return result


def database_thirpage():
    data = database2data()
    data = [(item[:-3] + (item[-3].replace("- ", ","), item[-2], item[-1].replace("- ", ","))) for item in data]
    result = defaultdict(list)  # 最顶层的聚类结果
    topic_layer = defaultdict(list)  # 次层的聚类结果
    id2tokeywords = defaultdict(list)
    for item in data:
        key = item[2]
        topic_layerid = item[4]
        result[key].append((item[0], item[1], item[3]))
        topic_layer[key].append(item[-2])
        id2tokeywords[topic_layerid].append(item[-1])
    result_linechart = {}
    with concurrent.futures.ThreadPoolExecutor() as executor:
        futures = []
        for i in result.keys():
            sorted_data = sorted(result[i], key=lambda x: x[1])
            sorted_dates = [item[1] for item in sorted_data]
            counter = Counter(sorted_dates)
            datetimes = list(counter.keys())
            counts = list(counter.values())
            futures.append(executor.submit(lambda: [(datetimes, counts)]))
        for i, future in enumerate(futures):
            result_linechart[i] = future.result()
    id2tokeywords_new = {i: [id2tokeywords[i][0], len(id2tokeywords[i])] for i in id2tokeywords.keys()}
    topic_layer_new = {i: list(set(topic_layer[i])) for i in topic_layer.keys()}
    return result, topic_layer_new, id2tokeywords_new, result_linechart


def get_all_base64_image():
    folder_path = '/data/shilong/wordcloud2/code/static/image'  # 文件夹路径
    result_base64 = {}
    for root, dirs, files in os.walk(folder_path):
        for file in files:
            file_path = os.path.join(root, file)
            with open(file_path, 'rb') as image_file:
                result_base64[file] = base64.b64encode(image_file.read()).decode('utf-8')
    return result_base64
